use wiki_db;

CREATE TABLE Author_Table
             (Author_ID           CHAR(8)         PRIMARY KEY,
              Name                CHAR(22)        NOT NULL);

CREATE VIEW      Author_View
       AS SELECT Author_ID,
                 Name
       FROM      Author_Table;
 
CREATE TABLE Book_Table
             (ISBN_13             CHAR(13)         PRIMARY KEY,
              Main_Author_ID      CHAR(8)          NOT NULL,
              Title               CHAR(32)         NOT NULL,
              Price               DECIMAL(6,2)     NOT NULL,
              FOREIGN KEY(Main_Author_ID)
              REFERENCES Author_Table (Author_ID));
 
CREATE VIEW      Book_View
       AS SELECT ISBN_13,
                 Main_Author_ID,
                 Title,
                 Price
       FROM      Book_Table;
 
CREATE TABLE Specimen_Table
             (Book_ID             CHAR(8)          PRIMARY KEY,
              ISBN_13             CHAR(13)         NOT NULL,
              Shelf_Position      CHAR(30)         NOT NULL,
              FOREIGN KEY(ISBN_13)
              REFERENCES Book_Table (ISBN_13));
 
CREATE VIEW      Specimen_View
       AS SELECT Book_ID,
                 ISBN_13,
                 Shelf_Position
       FROM      Specimen_Table;
 
CREATE TABLE Borrower_Table
             (Card_ID             CHAR(8)          PRIMARY KEY,
              Card_Issue_Date     DATE             NOT NULL,
              Name                VARCHAR(50)      NOT NULL,
              Address             VARCHAR(50)      NOT NULL);
 
CREATE VIEW      Borrower_View
       AS SELECT Card_ID,
                 Card_Issue_Date,
                 Name,
                 Address
       FROM      Borrower_Table;
 
CREATE TABLE Borrow_Table
             (Card_ID             CHAR(8)          NOT NULL,
              Book_ID             CHAR(8)          NOT NULL,
              PRIMARY KEY(Card_ID, Book_ID),
              FOREIGN KEY(Card_ID)
              REFERENCES Borrower_Table (Card_ID),
              FOREIGN KEY(Book_ID)
              REFERENCES Specimen_Table (Book_ID)
              ON DELETE CASCADE
              ON UPDATE RESTRICT);
 
CREATE VIEW      Borrow_View
       AS SELECT Card_ID,
                 Book_ID
       FROM      Borrow_Table;

CREATE TABLE Borrow_Event_Table
             (Card_ID             CHAR(8)          NOT NULL,
              Book_ID             CHAR(8)          NOT NULL,
              Borrow_Date         DATE             NOT NULL,
              Planned_Return_Date DATE             NOT NULL,
              Real_Return_Date    DATE             NULL,
              PRIMARY KEY(Card_ID, Book_ID,Borrow_Date),
              FOREIGN KEY(Card_ID)
              REFERENCES Borrow_Table (Card_ID),
              FOREIGN KEY(Book_ID)
              REFERENCES Borrow_Table (Book_ID)
              ON DELETE CASCADE
              ON UPDATE RESTRICT);
 
CREATE VIEW      Borrow_Event_View
       AS SELECT Card_ID,
                 Book_ID,
                 Borrow_Date,
                 Planned_Return_Date,
                 Real_Return_Date
       FROM      Borrow_Event_Table;